<!DOCTYPE html>
<html lang="en">
<head>
    <title>囧么肥事</title>
    <meta charset="UTF-8"/>
    <meta name="viewport"
          content="width=device-width,height=device-height,initial-scale=1.0,maximum-scale=1.0,user-scalable=0"/>
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
    <meta http-equiv="Content-Type" content="text/html;"/>
    <meta name="renderer" content="webkit">
    <!--解决页面加载网络图片的403问题 , 可以让img标签预加载网络图片-->
    <meta name="referrer" content="no-referrer" />

    <!--markdown S -->
    <link rel="stylesheet" type="text/css" href="../../static/css/export_setting_css.css"/>
    <link rel="stylesheet" type="text/css" href="../../static/css/markdown_preview_css.css"/>
    <!--markdown E -->

    <!--left bar tools css E-->
    <link rel="stylesheet" type="text/css" href="../../static/css/nav.css">
    <link rel="stylesheet" type="text/css" href="http://www.jq22.com/jquery/font-awesome.4.6.0.css">
    <!--left bar tools css E-->

    <!-- 滚动条 S -->
    <link rel="stylesheet" href="../../static/css/optiscroll.css">
    <!-- 滚动条 E -->
</head>
<body>
<div id="m-wrapper" class="m-wrapper optiscroll">
    <!-- top 锚点 -->
    <a id="top"></a>

    <!-- left bar tools S -->
    <div class="leftNav-item">
        <ul>
            <li>
                <i class="fa fa-location-arrow"></i>
                <a href="http://jiongmefeishi.gitee.io/monster-blog/" target="_blank" class="rota">个人网站</a>
            </li>

            <li title="Git">
                <i class="fa fa-code-fork" aria-hidden="true"></i>
                <a href="http://jiongmefeishi.gitee.io/monster-blog/" target="_blank" class="rota">Git Page</a>
            </li>

            <li lay-data="img" data-fooc="http://resource.zqtaotao.cn/wx/qrcode/jiongmefeishi.jpg">
                <i class="fa fa-comments-o"></i><a href="javascript:;" class="rota">公众号</a>
            </li>

            <li lay-data="img" data-fooc="http://resource.zqtaotao.cn/weixincode.png">
                <i class="fa fa-money"></i><a href="javascript:;" class="rota">小额打赏</a>
            </li>
            <li title="返回顶部" class="for-top">
                <i class="fa fa-arrow-up"></i>
                <a href="#top" class="rota">去顶部</a>
            </li>
        </ul>
    </div>
    <!-- left bar tools E -->

    <!--content S-->

    <div id="export_content"><div class="output_wrapper" id="output_wrapper_id"><h1 id="hmysql"><span>《MySQL面试小抄》索引考点二面总结</span></h1>
        <p>我是肥哥，一名不专业的面试官！</p>
        <p>我是囧囧，一名积极找工作的小菜鸟！</p>
        <blockquote>
            <p><strong>囧囧表示：小白面试最怕的就是面试官问的知识点太笼统，自己无法快速定位到关键问题点！！！</strong></p>
        </blockquote>
        <hr>
        <p>本期主要面试考点</p>
        <pre><code class="hljs">面试官考点之谈谈索引维护过程？页分裂？页合并？<br></code></pre>
        <pre><code class="hljs">面试官考点之简述一下查询时B+树索引搜索过程？<br></code></pre>
        <pre><code class="hljs">面试官考点之什么是回表？<br></code></pre>
        <pre><code class="hljs">面试官考点之什么是索引覆盖？使用场景？<br></code></pre>
        <pre><code class="hljs">面试官考点之什么情况下会索引失效？<br></code></pre>
        <pre><code class="hljs">面试官考点之哪些情况下，可能会面临索引失效的问题？<br></code></pre>
        <pre><code class="hljs coffeescript">面试官考点之<span class="hljs-keyword">or</span>走索引和索引失效分别是什么场景？<br></code></pre>
        <pre><code class="hljs">面试官考点之哪些情况下需要创建索引？<br></code></pre>
        <pre><code class="hljs">面试官考点之联合索引之最左前缀原则？<br></code></pre>
        <pre><code class="hljs">面试官考点之索引下推场景？<br></code></pre>
        <hr>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/432b0e67643f74b3760d42fd4882df2b.png" alt="索引二面1" title="索引二面1"><figcaption>索引二面1</figcaption></figure>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/7bfbfcf63155605af95ddb96b336983d.png" alt="索引二面2" title="索引二面2"><figcaption>索引二面2</figcaption></figure>
        <hr>
        <h3 id="h"><span>面试官考点之谈谈索引维护过程？页分裂？页合并？</span></h3>
        <p>B+树为了维护索引有序性，在插入删除的时候需要做必要的维护，必要时候可能涉及到<strong>页分裂，页合并</strong>过程！</p>
        <p>首先假设每个叶子节点（数据页或磁盘块）只能存储3条索引和数据记录，如图</p>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/1d4fafb16cee1b148bd6692c03eb0dae.png" alt="ID索引树" title="ID索引树"><figcaption>ID索引树</figcaption></figure>
        <blockquote>
            <p>情况1、新增行记录，ID=3，此时【数据页1】未满，只需要在data2后新增ID=3的行记录，B+树整体结构不需要进行调整</p>
        </blockquote>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/223327cac659a5f5ce7df3f95e488537.png" alt="索引页分裂" title="索引页分裂"><figcaption>索引页分裂</figcaption></figure>
        <blockquote>
            <p>情况2、新增行记录，ID=8，此时【数据页2】已满，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为<strong>页分裂</strong>。</p>
        </blockquote>
        <p>页分裂过程消耗性能，同时空间利用率也降低了</p>
        <p>有分裂就有合并，当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。<strong>合并的过程，可以认为是分裂过程的逆过程</strong>。</p>
        <p>当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。</p>
        <p>【数据页2】删除了ID=7,ID=8的行记录，此时【数据页2】【数据页3】利用率很低，将进行页合并。</p>
        <h3 id="hb"><span>面试官考点之简述一下查询时B+树索引搜索过程？</span></h3>
        <p>准备一张用户表，其中id为主键，age为普通索引</p>
        <pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span>&nbsp;<span class="hljs-keyword">TABLE</span>&nbsp;<span class="hljs-string">`user`</span>&nbsp;(<br>&nbsp;&nbsp;<span class="hljs-string">`id`</span>&nbsp;<span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>)&nbsp;PRIMARY&nbsp;<span class="hljs-keyword">KEY</span>,<br>&nbsp;&nbsp;<span class="hljs-string">`name`</span>&nbsp;<span class="hljs-built_in">varchar</span>(<span class="hljs-number">255</span>)&nbsp;<span class="hljs-keyword">DEFAULT</span>&nbsp;<span class="hljs-literal">NULL</span>,<br>&nbsp;&nbsp;<span class="hljs-string">`age`</span>&nbsp;<span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>)&nbsp;<span class="hljs-keyword">DEFAULT</span>&nbsp;<span class="hljs-literal">NULL</span><br>&nbsp;&nbsp;<span class="hljs-keyword">KEY</span>&nbsp;<span class="hljs-string">`idx_age`</span>&nbsp;(<span class="hljs-string">`age`</span>)&nbsp;<span class="hljs-keyword">USING</span>&nbsp;BTREE<br>)&nbsp;<span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">InnoDB</span>&nbsp;<span class="hljs-keyword">DEFAULT</span>&nbsp;<span class="hljs-keyword">CHARSET</span>=utf8;<br></code></pre>
        <p>select * from user where age=22 简述一下B+树索引搜索过程？</p>
        <p>假设要查询的记录</p>
        <pre><code class="hljs objectivec">&nbsp;<span class="hljs-keyword">id</span>=<span class="hljs-number">5</span>,name=<span class="hljs-string">"张三"</span>,age=<span class="hljs-number">22</span><br></code></pre>
        <p>MySQL为每个索引分别维护了一棵B+Tree索引树，</p>
        <p>主键索引非叶子节点维护了索引键，叶子节点存储行数据；</p>
        <p>非主键索引也称为二级索引，非叶子节点存储主键；</p>
        <p><strong>B+树索引搜索过程</strong></p>
        <p>搜索条件 age=22，可走idx_age索引，首先加载idx_age索引树，找到age=22的记录，取得id=5</p>
        <p>回表搜索，加载主键索引树，找到id=22的记录，取得整行数据</p>
        <h3 id="h-1"><span>面试官考点之什么是回表？</span></h3>
        <p>idx_age二级索引树找到主键id后，回到id主键索引搜索的过程,就称为回表。</p>
        <p>并非所有非主键索引搜索，都需要进行回表搜索，也就是下面要说的索引覆盖。</p>
        <h3 id="h-2"><span>面试官考点之什么是索引覆盖？使用场景？</span></h3>
        <p>在上面提到的例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。</p>
        <p>如果在查询的数据列里面，直接从索引列就能取到想要的结果，就不需要再回表去查，也称之为索引覆盖！</p>
        <p><strong>索引覆盖的优点</strong></p>
        <blockquote>
            <ol>
                <li><span>可以避免对Innodb主键索引的二次查询</span></li>
                <li><span>可以避免MyISAM表进行系统调用</span></li>
                <li><span>可以优化缓存,减少磁盘IO操作</span></li>
            </ol>
            <p><strong>修改一下上述栗子，满足索引覆盖条件？</strong></p>
        </blockquote>
        <pre><code class="hljs sql"><span class="hljs-keyword">select</span>&nbsp;<span class="hljs-keyword">id</span>,&nbsp;age&nbsp;<span class="hljs-keyword">from</span>&nbsp;<span class="hljs-keyword">user</span>&nbsp;<span class="hljs-keyword">where</span>&nbsp;age=<span class="hljs-number">22</span><br></code></pre>
        <p>查询的信息，id，age都可以直接在idx_age 索引树中获取，不需要回表搜索。</p>
        <p>由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用<br>的性能优化手段。</p>
        <p>索引是一把双刃剑，提供快速排序搜索的同时，索引字段的维护也是要付出相应的代价的。</p>
        <p>因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了</p>
        <h3 id="h-3"><span>面试官考点之索引失效？</span></h3>
        <blockquote>
            <p><strong>创建的索引，到底有没有生效，或者说SQL语句有没有使用索引查询？</strong></p>
        </blockquote>
        <p>一个最常见的查询场景，建立idx_name索引</p>
        <pre><code class="hljs sql"><span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;user_name&nbsp;<span class="hljs-keyword">like</span>&nbsp;<span class="hljs-string">'%mayun100%'</span>;<br></code></pre>
        <blockquote>
            <p>这条查询是否走索引？</p>
        </blockquote>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/aa2edacc63216a4b3df37ab47f127243.png" alt="like不走索引" title="like不走索引"><figcaption>like不走索引</figcaption></figure>
        <pre><code class="hljs sql"><span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;user_name&nbsp;<span class="hljs-keyword">like</span>&nbsp;<span class="hljs-string">'mayun100%'</span>;<br></code></pre>
        <blockquote>
            <p>这条查询是否走索引？</p>
        </blockquote>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/354da47e38981d61c430c3df501add63.png" alt="like走索引" title="like走索引"><figcaption>like走索引</figcaption></figure>
        <h3 id="h-4"><span>面试官考点之有哪些情况下，可能会面临索引失效的问题？</span></h3>
        <blockquote>
            <ol>
                <li><span>like通配符，左侧开放情况下，全表扫描</span></li>
                <li><span>or条件筛选，可能会导致索引失效</span></li>
                <li><span>where中对索引列使用mysql的内置函数，一定失效</span></li>
                <li><span>where中对索引列进行运算（如，+、-、*、/），一定失效</span></li>
                <li><span>类型不一致，隐式的类型转换，导致的索引失效</span></li>
                <li><span>where语句中索引列使用了负向查询，可能会导致索引失效 负向查询包括：NOT、!=、&lt;&gt;、!&lt;、!&gt;、NOT IN、NOT LIKE等。其中!< !> 为SQLServer语法。</span></li>
                <li><span>索引字段可以为null，使用is null或is not null时，可能会导致索引失效</span></li>
                <li><span>隐式字符编码转换导致的索引失效</span></li>
                <li><span>联合索引中，where中索引列违背最左匹配原则，一定会导致索引失效</span></li>
                <li><span>MySQL优化器的最终选择，不走索引</span></li>
            </ol>
        </blockquote>
        <h3 id="hor"><span>面试官考点之or走索引和索引失效分别是什么场景?</span></h3>
        <blockquote>
            <p>or走索引和索引失效分别是什么场景?</p>
        </blockquote>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/c39a579b53855528c57ee89d4c6d1bf6.png" alt="or查询" title="or查询"><figcaption>or查询</figcaption></figure>
        <blockquote>
            <p>OR 连接的是同一个字段，相同走索引</p>
        </blockquote>
        <pre><code class="hljs sql"><span class="hljs-keyword">explain</span>&nbsp;<span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;user_name&nbsp;=&nbsp;<span class="hljs-string">'mayun10'</span>&nbsp;<span class="hljs-keyword">or</span>&nbsp;user_name&nbsp;=&nbsp;<span class="hljs-string">'mayun1000'</span><br></code></pre>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/43856df21b9d98c223f4f268c1609924.png" alt="or查询走索引情况" title="or查询走索引情况"><figcaption>or查询走索引情况</figcaption></figure>
        <blockquote>
            <p>OR 连接的是两个不同的字段，不走索引</p>
        </blockquote>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/43b9f2d0a4273f34adcb1b3a261d5b28.png" alt="or查询索引失效情况" title="or查询索引失效情况"><figcaption>or查询索引失效情况</figcaption></figure>
        <blockquote>
            <p>给address列增加索引</p>
        </blockquote>
        <pre><code class="hljs sql"><span class="hljs-keyword">alter</span>&nbsp;<span class="hljs-keyword">table</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">add</span>&nbsp;<span class="hljs-keyword">index</span>&nbsp;idx_address(address);<br><span class="hljs-keyword">explain</span>&nbsp;<span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;user_name&nbsp;=&nbsp;<span class="hljs-string">'mayun10'</span>&nbsp;<span class="hljs-keyword">or</span>&nbsp;address&nbsp;=&nbsp;<span class="hljs-string">'浙江杭州12'</span>;<br></code></pre>
        <blockquote>
            <p>OR 连接的是两个不同字段，如果两个字段皆有索引，走索引</p>
        </blockquote>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/0265f0dfc884568686006149a1bf9b0f.png" alt="or查询走索引情况-两边字段有索引" title="or查询走索引情况-两边字段有索引"><figcaption>or查询走索引情况-两边字段有索引</figcaption></figure>
        <blockquote>
            <p>（插播，下一期：《MySQL面试小抄》几种索引失效场景验证）</p>
            <p>尽请关注：囧么肥事</p>
            <p>面试小抄系列。</p>
        </blockquote>
        <h3 id="h-5"><span>面试官考点之哪些情况下需要创建索引？</span></h3>
        <blockquote>
            <p>1.主键自动建立唯一索引</p>
            <p>2.频繁查询的字段</p>
            <p>3.JOIN 关联查询，作为外键关系的列建立索引</p>
            <p>4.单键/组合索引的选择问题，高并发下倾向创建组合索引，创建时遵循最左前缀匹配原则</p>
            <p>5.ORDER BY 查询中排序的字段，排序字段通过索引访问大幅提高排序速度</p>
            <p>6.GROUP BY 需要分组字段或查询中统计字段</p>
        </blockquote>
        <h3 id="h-6"><span>面试官考点之联合索引之最左前缀原则</span></h3>
        <p>MySQL建立多列索引（联合索引）有最左前缀的原则，即最左优先</p>
        <p><strong>当MySQL建立的是联合索引，假设以(a,b,c) 列作为联合索引，那么MySQL建树规则是什么？</strong></p>
        <p>我们知道MySQL会为每一个索引维护一颗B+Tree，非叶子节点存储索引key，叶子节点存储行数据data。</p>
        <p>联合索引(a,b,c) 相当于建立了 (a), (a,b), (a,b,c) 三个索引，MySQL组装索引树时，是按照从左到右的顺序来建立B+Tree的联合索引树的。</p>
        <h4 id="h-7"><span>匹配索引情况一</span></h4>
        <blockquote>
            <p><strong>假设（a,b,c）</strong>索引要搜索的值为('张三', 21, 100) ，检索数据时，匹配的顺序就是a,b,c。</p>
            <p>B+Tree会优先比较a来确定下一步的所搜方向，如果a相同再依次比较b和c，最后得到检索的数据；</p>
        </blockquote>
        <h4 id="h-8"><span>匹配索引情况二</span></h4>
        <blockquote>
            <p><strong>假设（a,c）</strong>索引要搜索的值为('张三', 100) ，检索数据时，匹配的顺序就是a,b,c。</p>
            <p>B+Tree使用a来指定搜索方向，但下一个字段b缺失，所以只能把a等于张三的数据都找到，然后再匹配c是100的数据。</p>
        </blockquote>
        <h4 id="h-9"><span>匹配索引情况三</span></h4>
        <blockquote>
            <p><strong>假设（b,c）</strong>索引要搜索的值为('张三', 21) ，检索数据时，无匹配顺序</p>
            <p>B+Tree不知道下一步该查哪个节点，因为建立搜索树的时候a是第一个比较因子，必须要先根据a来搜索才能知道下一步去哪里查询。此时索引失效！</p>
        </blockquote>
        <p>索引项是按照索引定义里面出现的字段顺序排序的，<strong>最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。</strong></p>
        <h3 id="h-10"><span>面试官考点之索引下推场景？</span></h3>
        <p>索引下推，即减少二级索引回表搜索次数！！！</p>
        <p>通俗说，减少查询主键索引树次数，减少磁盘IO</p>
        <p>建立联合索引 idx_age_weight</p>
        <pre><code class="hljs sql"><span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;<span class="hljs-keyword">user</span>&nbsp;<span class="hljs-keyword">where</span>&nbsp;age&nbsp;=&nbsp;<span class="hljs-number">11</span>&nbsp;<span class="hljs-keyword">and</span>&nbsp;weight&nbsp;=&nbsp;<span class="hljs-number">98</span><br></code></pre>
        <p><strong>5.6之前搜索过程是</strong></p>
        <p>在idx_age_weight 索引树中匹配出所有的 age = 11 索引，拿到主键id，回表去一条条再比对weight字段</p>
        <p>如下图，需要进行3次回表搜索操作</p>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/6be7b26c140143f37bbfaa269df172b5.png" alt="5.6之前回表操作" title="5.6之前回表操作"><figcaption>5.6之前回表操作</figcaption></figure>
        <p><strong>5.6后的搜索过程是</strong><br>在idx_age_weight 索引树中匹配出所有的 age = 11 索引，顺便对weight字段进行判断，过滤掉weight = 100的记录，然后再进行回表搜索。</p>
        <p>如下图，只需要进行2次回表搜索操作</p>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/089159e9b5c3b31c81db3cf52d1bf747.png" alt="5.6后索引下推" title="5.6后索引下推"><figcaption>5.6后索引下推</figcaption></figure>
        <p>阅读原文：</p>
        <p><a href="https://mp.weixin.qq.com/s/2jC8AwsH8cpk8T23tN0bgg">《MySQL面试小抄》索引考点二面总结</a></p>
        <p><a href="https://mp.weixin.qq.com/s/TMa6PT070inUtURl48URuQ">《MySQL面试小抄》索引考点一面总结</a></p>
        <p>随缘更新，整理不易，欢迎联系小白讨论，大神巴巴请绕路！</p>
        <p>更多精彩内容，欢迎关注微信公众号：<strong>囧么肥事</strong> (或搜索：jiongmefeishi)</p>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/cb3a296f8edbcc70370d4eb569c40634.png" alt="囧么肥事" title="囧么肥事"><figcaption>囧么肥事</figcaption></figure></div></div>

    <!--content E-->
</div>



</body>
<!--left bar tools js S-->
<script src="http://www.jq22.com/jquery/jquery-1.10.2.js"></script>
<script type="text/javascript" src="../../static/js/nav.js"></script>
<!--left bar tools js E-->

<!-- 滚动条 S -->
<script type="text/javascript" src="../../static/js/optiscroll.js"></script>
<!-- 滚动条 E -->

<!--commonjs S-->
<script type="text/javascript" src="../../static/js/common.js"></script>
<!--commonjs E-->

</html>
